{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>目录<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#拼接数据\" data-toc-modified-id=\"拼接数据-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>拼接数据</a></span></li><li><span><a href=\"#算数计算\" data-toc-modified-id=\"算数计算-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>算数计算</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "import pandas as pd\n",
    "conn = mysql.connector.connect(host='127.0.0.1',\n",
    "                      user='iroan',\n",
    "                      password='iroanMYS47',\n",
    "                      database = 'practice_sql')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>vend_id</th>\n",
       "      <th>vend_name</th>\n",
       "      <th>vend_address</th>\n",
       "      <th>vend_city</th>\n",
       "      <th>vend_state</th>\n",
       "      <th>vend_zip</th>\n",
       "      <th>vend_country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BRE02</td>\n",
       "      <td>Bear Emporium</td>\n",
       "      <td>500 Park Street</td>\n",
       "      <td>Anytown</td>\n",
       "      <td>OH</td>\n",
       "      <td>44333</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BRS01</td>\n",
       "      <td>Bears R Us</td>\n",
       "      <td>123 Main Street</td>\n",
       "      <td>Bear Town</td>\n",
       "      <td>MI</td>\n",
       "      <td>44444</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DLL01</td>\n",
       "      <td>Doll House Inc.</td>\n",
       "      <td>555 High Street</td>\n",
       "      <td>Dollsville</td>\n",
       "      <td>CA</td>\n",
       "      <td>99999</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FNG01</td>\n",
       "      <td>Fun and Games</td>\n",
       "      <td>42 Galaxy Road</td>\n",
       "      <td>London</td>\n",
       "      <td>None</td>\n",
       "      <td>N16 6PS</td>\n",
       "      <td>England</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FRB01</td>\n",
       "      <td>Furball Inc.</td>\n",
       "      <td>1000 5th Avenue</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>11111</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>JTS01</td>\n",
       "      <td>Jouets et ours</td>\n",
       "      <td>1 Rue Amusement</td>\n",
       "      <td>Paris</td>\n",
       "      <td>None</td>\n",
       "      <td>45678</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  vend_id        vend_name     vend_address   vend_city vend_state vend_zip  \\\n",
       "0   BRE02    Bear Emporium  500 Park Street     Anytown         OH    44333   \n",
       "1   BRS01       Bears R Us  123 Main Street   Bear Town         MI    44444   \n",
       "2   DLL01  Doll House Inc.  555 High Street  Dollsville         CA    99999   \n",
       "3   FNG01    Fun and Games   42 Galaxy Road      London       None  N16 6PS   \n",
       "4   FRB01     Furball Inc.  1000 5th Avenue    New York         NY    11111   \n",
       "5   JTS01   Jouets et ours  1 Rue Amusement       Paris       None    45678   \n",
       "\n",
       "  vend_country  \n",
       "0          USA  \n",
       "1          USA  \n",
       "2          USA  \n",
       "3      England  \n",
       "4          USA  \n",
       "5       France  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select * from Vendors;'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 拼接数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>concat(vend_name,'[',vend_country,']')</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bear Emporium[USA]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bears R Us[USA]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Doll House Inc.[USA]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fun and Games[England]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Furball Inc.[USA]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Jouets et ours[France]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  concat(vend_name,'[',vend_country,']')\n",
       "0                     Bear Emporium[USA]\n",
       "1                        Bears R Us[USA]\n",
       "2                   Doll House Inc.[USA]\n",
       "3                 Fun and Games[England]\n",
       "4                      Furball Inc.[USA]\n",
       "5                 Jouets et ours[France]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select concat(vend_name,'[',vend_country,']') from Vendors;'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>vend_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bear Emporium[USA]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bears R Us[USA]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Doll House Inc.[USA]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fun and Games[England]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Furball Inc.[USA]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Jouets et ours[France]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               vend_title\n",
       "0      Bear Emporium[USA]\n",
       "1         Bears R Us[USA]\n",
       "2    Doll House Inc.[USA]\n",
       "3  Fun and Games[England]\n",
       "4       Furball Inc.[USA]\n",
       "5  Jouets et ours[France]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select concat(vend_name,'[',vend_country,']') as vend_title from Vendors;'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 算数计算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_num</th>\n",
       "      <th>order_item</th>\n",
       "      <th>prod_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>item_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20005</td>\n",
       "      <td>1</td>\n",
       "      <td>BR01</td>\n",
       "      <td>100</td>\n",
       "      <td>5.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20005</td>\n",
       "      <td>2</td>\n",
       "      <td>BR03</td>\n",
       "      <td>100</td>\n",
       "      <td>10.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20006</td>\n",
       "      <td>1</td>\n",
       "      <td>BR01</td>\n",
       "      <td>20</td>\n",
       "      <td>5.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20006</td>\n",
       "      <td>2</td>\n",
       "      <td>BR02</td>\n",
       "      <td>10</td>\n",
       "      <td>8.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20006</td>\n",
       "      <td>3</td>\n",
       "      <td>BR03</td>\n",
       "      <td>10</td>\n",
       "      <td>11.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20007</td>\n",
       "      <td>1</td>\n",
       "      <td>BR03</td>\n",
       "      <td>50</td>\n",
       "      <td>11.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>20007</td>\n",
       "      <td>2</td>\n",
       "      <td>BNBG01</td>\n",
       "      <td>100</td>\n",
       "      <td>2.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>20007</td>\n",
       "      <td>3</td>\n",
       "      <td>BNBG02</td>\n",
       "      <td>100</td>\n",
       "      <td>2.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>20007</td>\n",
       "      <td>4</td>\n",
       "      <td>BNBG03</td>\n",
       "      <td>100</td>\n",
       "      <td>2.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>20007</td>\n",
       "      <td>5</td>\n",
       "      <td>RGAN01</td>\n",
       "      <td>50</td>\n",
       "      <td>4.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>20008</td>\n",
       "      <td>1</td>\n",
       "      <td>RGAN01</td>\n",
       "      <td>5</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>20008</td>\n",
       "      <td>2</td>\n",
       "      <td>BR03</td>\n",
       "      <td>5</td>\n",
       "      <td>11.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>20008</td>\n",
       "      <td>3</td>\n",
       "      <td>BNBG01</td>\n",
       "      <td>10</td>\n",
       "      <td>3.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>20008</td>\n",
       "      <td>4</td>\n",
       "      <td>BNBG02</td>\n",
       "      <td>10</td>\n",
       "      <td>3.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>20008</td>\n",
       "      <td>5</td>\n",
       "      <td>BNBG03</td>\n",
       "      <td>10</td>\n",
       "      <td>3.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>20009</td>\n",
       "      <td>1</td>\n",
       "      <td>BNBG01</td>\n",
       "      <td>250</td>\n",
       "      <td>2.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>20009</td>\n",
       "      <td>2</td>\n",
       "      <td>BNBG02</td>\n",
       "      <td>250</td>\n",
       "      <td>2.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>20009</td>\n",
       "      <td>3</td>\n",
       "      <td>BNBG03</td>\n",
       "      <td>250</td>\n",
       "      <td>2.49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    order_num  order_item prod_id  quantity  item_price\n",
       "0       20005           1    BR01       100        5.49\n",
       "1       20005           2    BR03       100       10.99\n",
       "2       20006           1    BR01        20        5.99\n",
       "3       20006           2    BR02        10        8.99\n",
       "4       20006           3    BR03        10       11.99\n",
       "5       20007           1    BR03        50       11.49\n",
       "6       20007           2  BNBG01       100        2.99\n",
       "7       20007           3  BNBG02       100        2.99\n",
       "8       20007           4  BNBG03       100        2.99\n",
       "9       20007           5  RGAN01        50        4.49\n",
       "10      20008           1  RGAN01         5        4.99\n",
       "11      20008           2    BR03         5       11.99\n",
       "12      20008           3  BNBG01        10        3.49\n",
       "13      20008           4  BNBG02        10        3.49\n",
       "14      20008           5  BNBG03        10        3.49\n",
       "15      20009           1  BNBG01       250        2.49\n",
       "16      20009           2  BNBG02       250        2.49\n",
       "17      20009           3  BNBG03       250        2.49"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select * from OrderItems;'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>item_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>RGAN01</td>\n",
       "      <td>5</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BR03</td>\n",
       "      <td>5</td>\n",
       "      <td>11.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BNBG01</td>\n",
       "      <td>10</td>\n",
       "      <td>3.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BNBG02</td>\n",
       "      <td>10</td>\n",
       "      <td>3.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BNBG03</td>\n",
       "      <td>10</td>\n",
       "      <td>3.49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prod_id  quantity  item_price\n",
       "0  RGAN01         5        4.99\n",
       "1    BR03         5       11.99\n",
       "2  BNBG01        10        3.49\n",
       "3  BNBG02        10        3.49\n",
       "4  BNBG03        10        3.49"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select prod_id, quantity,item_price from OrderItems where order_num = 20008;'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>item_price</th>\n",
       "      <th>b'expended_price'</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>RGAN01</td>\n",
       "      <td>5</td>\n",
       "      <td>4.99</td>\n",
       "      <td>24.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BR03</td>\n",
       "      <td>5</td>\n",
       "      <td>11.99</td>\n",
       "      <td>59.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BNBG01</td>\n",
       "      <td>10</td>\n",
       "      <td>3.49</td>\n",
       "      <td>34.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BNBG02</td>\n",
       "      <td>10</td>\n",
       "      <td>3.49</td>\n",
       "      <td>34.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BNBG03</td>\n",
       "      <td>10</td>\n",
       "      <td>3.49</td>\n",
       "      <td>34.90</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prod_id  quantity  item_price  b'expended_price'\n",
       "0  RGAN01         5        4.99              24.95\n",
       "1    BR03         5       11.99              59.95\n",
       "2  BNBG01        10        3.49              34.90\n",
       "3  BNBG02        10        3.49              34.90\n",
       "4  BNBG03        10        3.49              34.90"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select prod_id, quantity,item_price, quantity * item_price as expended_price from OrderItems where order_num = 20008;'''\n",
    ",conn)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "base"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {
    "height": "12px",
    "width": "163px"
   },
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "目录",
   "title_sidebar": "大纲",
   "toc_cell": true,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
